package groups.invertebrates.dao;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;

import groups.invertebrates.InvertebrateVectorOfAgent;

public class JDBCInvertebrateVectorOfAgentDao implements IInvertebrateVectorOfAgentDao{

	private Connection con;

	public JDBCInvertebrateVectorOfAgentDao(Connection con){
		try {
			Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		this.con = con;
	}

	public void create(int vectorInvetebrateSpecies, int carriedEtiologicalAgent){
		InvertebrateVectorOfAgent invertebrateVectorOfAgent = new InvertebrateVectorOfAgent();
		invertebrateVectorOfAgent.setVectorInvetebrateSpecies(vectorInvetebrateSpecies);
		invertebrateVectorOfAgent.setCarriedEtiologicalAgent(carriedEtiologicalAgent);
	
		insert(invertebrateVectorOfAgent);
	}

	public void delete(int idInvertebrateVectorOfAgent) {		
		try {
			Statement stmt = con.createStatement();
			stmt.executeUpdate("DELETE FROM APP.INVERTEBRATEVECTOROFAGENT WHERE IDINVERTEBRATEVECTOROFAGENT = " + idInvertebrateVectorOfAgent);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public InvertebrateVectorOfAgent read(int idInvertebrateVectorOfAgent) {
		InvertebrateVectorOfAgent invertebrateVectorOfAgent= new InvertebrateVectorOfAgent();
		try {
			ResultSet result;
			Statement stmt = con.createStatement();
			result = stmt.executeQuery("SELECT * FROM APP.INVERTEBRATEVECTOROFAGENT WHERE IDINVERTEBRATEVECTOROFAGENT = " + idInvertebrateVectorOfAgent);
			//invertebrateVectorOfAgent.setVectorInvetebrateSpecies(idInvertebrateVectorOfAgent);
			result.next();
			invertebrateVectorOfAgent.setVectorInvetebrateSpecies(result.getInt("VECTORINVERTEBRATESPECIES"));
			invertebrateVectorOfAgent.setCarriedEtiologicalAgent(result.getInt("CARRIEDETIOLOGICALAGENT"));
			
			
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return invertebrateVectorOfAgent;
	}

	public Vector<InvertebrateVectorOfAgent> readAll() {
		Vector<InvertebrateVectorOfAgent> invertebrateVectorOfAgentVector = new Vector<InvertebrateVectorOfAgent>();
		try {
			ResultSet result;
			Statement stmt = con.createStatement();
			result = stmt.executeQuery("SELECT * FROM APP.INVERTEBRATEVECTOROFAGENT");
					while (result.next()){
						InvertebrateVectorOfAgent invertebrateVectorOfAgent = new InvertebrateVectorOfAgent();
						invertebrateVectorOfAgent.setVectorInvetebrateSpecies(result.getInt("VECTORINVERTEBRATESPECIES"));
						invertebrateVectorOfAgent.setCarriedEtiologicalAgent(result.getInt("CARRIEDETIOLOGICALAGENT"));						
						invertebrateVectorOfAgentVector.add(invertebrateVectorOfAgent);
					}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return invertebrateVectorOfAgentVector;

	}

	public void update(int idInvertebrateVectorOfAgent, int vectorInvetebrateSpecies, int carriedEtiologicalAgent){
		try {
			Statement stmt = con.createStatement();
			stmt.executeUpdate("UPDATE APP.INVERTEBRATEVECTOROFAGENT " +
					"SET INVERTEBRATEVECTOROFAGENT='"+ vectorInvetebrateSpecies + "', CARRIEDETIOLOGICALAGENT='" + carriedEtiologicalAgent);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	private void insert(InvertebrateVectorOfAgent invertebrateVectorOfAgent){
		try{
			int idInvertebrateVectorOfAgent;
			ResultSet result;
			Statement stmt = con.createStatement();
			result = stmt.executeQuery("SELECT MAX(IDINVERTEBRATEVECTOROFAGENT) AS IDINVERTEBRATEVECTOROFAGENT FROM APP.INVERTEBRATEVECTOROFAGENT");
			System.out.println(result.getCursorName());
			result.next();//Cursor na posicao da primeira tupla
			idInvertebrateVectorOfAgent = result.getInt("IDINVERTEBRATEVECTOROFAGENT") + 1;
			stmt.executeUpdate("INSERT INTO APP.INVERTEBRATEVECTOROFAGENT VALUES (" + 
					", '" + idInvertebrateVectorOfAgent + ", '" + invertebrateVectorOfAgent.getVectorInvetebrateSpecies() + "' , '" + invertebrateVectorOfAgent.getCarriedEtiologicalAgent() + "' , '"+ "')");
		}
		catch(SQLException e){
			e.printStackTrace();
		}

	}

}
